﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using System.Data.SqlClient;

namespace SQLServerHealthCheck.SQLServer
{
    public class CollectPerformanceTraceData
    {
        HealthCheckDataContext dc;

        public void CollectData(int traceid)
        {

            dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            var time = (from a in dc.Times

                        select a.Time1).Max();


            var server = from b in dc.Server_Traces
                         from S in dc.PerfServers
                         where b.PerfServerID == S.PerfServerID
                         && b.TraceID == traceid
                         select S;
            Time pTime = new Time();

            pTime.Time1 = DateTime.Now;



            pTime.TraceID = traceid;
            dc.Times.InsertOnSubmit(pTime);
            dc.SubmitChanges();
            foreach (var item in server)
            {
                new WMIQueries().GetDisk(item, pTime, dc);
                new WMIQueries().GetCPU(item, pTime, dc);
                collectSQLInstanceData(pTime, item, traceid);
            }


        }

        private void collectSQLInstanceData(Time time, PerfServer server, int traceid)
        {
            string connections = @"SELECT  COUNT(dbid) as TotalConnections FROM  sys.sysprocesses  WHERE   dbid > 0";
            string ple = @"SELECT [cntr_value] FROM sys.dm_os_performance_counters  WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'";

            string datareadwrites = @"SELECT name AS 'DBName' ,SUM(num_of_bytes_read) AS 'NumberOfRead' ,SUM(num_of_bytes_written) AS 'NumberOfWrites' FROM 
                         sys.dm_io_virtual_file_stats(NULL, NULL) I   INNER JOIN sys.databases D ON I.database_id = d.database_id GROUP BY name ";
            string databases = "SELECT name FROM master..sysdatabases";
            string waitstats = @" SELECT  
                                wait_type,  
                                wait_time_ms
                                FROM sys.dm_os_wait_stats 
                                WHERE wait_type  
                                NOT IN 
                                ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TRANSMITTER',
                                'CHECKPOINT_QUEUE','CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP', 
                                'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
                                'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK',
                                'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE',
                                'WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN','XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR',
                                'CLR_SEMAPHORE','BROKER_TO_FLUSH', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', 
                                'FT_IFTSHC_MUTEX', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')";

            var instances = from i in dc.Instances
                            from a in i.Instance_Traces
                            where i.PerfServer == server
                            && traceid == a.TraceID
                            select i;
            foreach (var instance in instances)
            {

                SQLServerInstance sqlServerInstance = new SQLServerInstance();
                sqlServerInstance.InstanceName = instance.Name;
                sqlServerInstance.Port = instance.Port;
                sqlServerInstance.Authentication = instance.Authentication;
                sqlServerInstance.Login = instance.Login;
                sqlServerInstance.Password = instance.Password;
                using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                {
                    try
                    {
                        cn.Open();

                        SqlCommand cmd = new SqlCommand(ple, cn);
                        SqlDataReader rd = cmd.ExecuteReader();
                        InstanceData data = new InstanceData();


                        while (rd.Read())
                        {
                            data.pageLifeExpectancy = Convert.ToInt32(rd.GetInt64(0));

                        }
                        rd.Close();
                        cmd = new SqlCommand(connections, cn);
                        rd = cmd.ExecuteReader();

                        while (rd.Read())
                        {
                            data.connections = Convert.ToInt32(rd.GetInt32(0));

                        }
                        rd.Close();
                        cmd = new SqlCommand(databases, cn);
                        rd = cmd.ExecuteReader();

                        while (rd.Read())
                        {

                            PerfDatabase database = new PerfDatabase();
                            database.name = Convert.ToString(rd.GetString(0));
                            var db = from a in dc.PerfDatabases
                                     where database.name == a.name && instance.InstanceID == a.InstanceID
                                     select a;

                            database.InstanceID = instance.InstanceID;
                            if (db.Count() == 0)
                            {
                                dc.PerfDatabases.InsertOnSubmit(database);
                                dc.SubmitChanges();
                            }

                        }
                        rd.Close();



                        cmd = new SqlCommand(datareadwrites, cn);
                        rd = cmd.ExecuteReader();

                        while (rd.Read())
                        {
                            PerfDatabaseData pdata = new PerfDatabaseData();

                            var datab = from a in dc.PerfDatabases
                                        where a.name == rd.GetString(0)
                                        && instance.InstanceID == a.InstanceID
                                        select a;
                            pdata.PerfDatabaseID = datab.First().PerfDatabaseID;
                            pdata.num_of_reads = Convert.ToInt64(rd.GetValue(1));
                            pdata.num_of_writes = Convert.ToInt64(rd.GetValue(2));
                            pdata.Time = time;
                            dc.PerfDatabaseDatas.InsertOnSubmit(pdata);
                        }
                        rd.Close();



                        data.Instance = instance;
                        data.Time = time;
                        dc.InstanceDatas.InsertOnSubmit(data);


                        cmd = new SqlCommand(waitstats, cn);
                        rd = cmd.ExecuteReader();

                        while (rd.Read())
                        {


                            PerfDatabaseData pdata = new PerfDatabaseData();
                            PerfWaitstat waitstat = new PerfWaitstat();
                            PerfWaitstatsData waitstatsdata = new PerfWaitstatsData();
                            waitstatsdata.wait_stat_s = Convert.ToInt32(rd.GetValue(1));
                            if (waitstatsdata.wait_stat_s != 0)
                            {
                                waitstat.Name = Convert.ToString(rd.GetString(0));

                                var waitstatexists = from a in dc.PerfWaitstats
                                                     where a.Name == waitstat.Name
                                            && instance.InstanceID == a.InstanceID
                                                     select a;

                                if (waitstatexists.Count() == 0)
                                {
                                    waitstat.InstanceID = instance.InstanceID;
                                    dc.PerfWaitstats.InsertOnSubmit(waitstat);
                                    dc.SubmitChanges();

                                    waitstatsdata.PerfWaitstatsID = waitstat.PerfWaitstatsID;
                                }
                                else
                                {
                                    waitstatsdata.PerfWaitstatsID = waitstatexists.First().PerfWaitstatsID;
                                }

                                waitstatsdata.TimeID = time.TimeID;
                                dc.PerfWaitstatsDatas.InsertOnSubmit(waitstatsdata);
                            }
                        }
                        rd.Close();

                    }
                    catch { }
                }

                dc.SubmitChanges();
                // execute sql statement here on the connection you created
            }

        }
    }



}

